﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Globalization;
using System.Collections.ObjectModel;
using System.Data;

namespace University.Workshop.DataAccess
{
    public static class OrderDataAccess
    {  
        private const string ConnectionString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static Collection<long> GetMostBought(long productId)
        {
            Collection<long> products = new Collection<long>();
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select ProductId, COUNT(*) from [LQSdataBase].[dbo].[Order] where BuyerId in (select BuyerId from [LQSdataBase].[dbo].[Order] where ProductId=@product and Status='Completed') and ProductId!=@product group by ProductId order by COUNT(*) desc";
                command.Parameters.AddWithValue("@product", productId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    long product = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting product list", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return products;
        }

        public static Collection<long> GetFrequentlyBoughtTogether(long productId)
        {
            Collection<long> products = new Collection<long>();
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select ProductId, COUNT(*) from [LQSdataBase].[dbo].[Order] where OrderId in (select OrderId from [LQSdataBase].[dbo].[Order] where ProductId=@product and Status='Completed') and ProductId!=@product group by ProductId order by COUNT(*) desc";
                command.Parameters.AddWithValue("@product", productId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    long product = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting product list", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return products;
        }

        public static Collection<long> GetMostBoughtByViewed(long productId)
        {
            Collection<long> products = new Collection<long>();
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select ProductId, COUNT(*) from [LQSdataBase].[dbo].[Order] where BuyerId in (select userId from [LQSdataBase].[dbo].[Views] where prodId=@product) and Status='Completed' and ProductId!=@product group by ProductId order by COUNT(*) desc";
                command.Parameters.AddWithValue("@product", productId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    long product = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting product list", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return products;
        }

        private static long GetNewOrderNumber()
        {
            SqlConnection connection = null;
            long orderNumber = 0;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT DISTINCT MAX(OrderId) AS MaxOrderId FROM [ORDER] O GROUP BY OrderId";
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return 1;
                }
                while (reader.Read())
                {
                    orderNumber = long.Parse(reader["MaxOrderId"].ToString(), CultureInfo.CurrentCulture) + 1;
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error accessing order database", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return orderNumber;
        }

        public static long AddNewOrder(Collection<Product> products, long buyerId)
        {
            if (products != null)
            {
                long orderNumber = GetNewOrderNumber();
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    connection.Open();
                    foreach (Product prod in products)
                    {
                        SqlCommand command = connection.CreateCommand();
                        command.CommandText = "INSERT INTO [ORDER](ProductId,BuyerId,OrderId,Quantity,Status) VALUES(@ProductId,@BuyerId,@OrderId,@Quantity,@Status)";
                        command.Parameters.AddWithValue("ProductId", prod.ProductId);
                        command.Parameters.AddWithValue("BuyerId", buyerId);
                        command.Parameters.AddWithValue("OrderId", orderNumber);
                        command.Parameters.AddWithValue("Quantity", prod.Quantity);
                        command.Parameters.AddWithValue("Status", "Processing");
                        int result = command.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error inserting an order");
                        }
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error adding an order", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
                return orderNumber;
            }
            return -1;
        }

        public static void CompleteOrder(long orderId)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "UPDATE [ORDER] SET Status=@Status WHERE OrderId=@OrderId";
                command.Parameters.AddWithValue("Status", "Completed");
                command.Parameters.AddWithValue("OrderId", orderId);
                SqlCommand updateProducts = connection.CreateCommand();
                updateProducts.CommandText = "Select ProductId,Quantity FROM [ORDER] WHERE OrderId=@OrderId";
                updateProducts.Parameters.AddWithValue("OrderId", orderId);
                int result = command.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error completing an order");
                }
                connection.Close();
                connection.Open();
                SqlDataReader reader = updateProducts.ExecuteReader();
                Collection<Product> products = new Collection<Product>();
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    products.Add(product);
                }
                ProductDataAccess.UpdateProducts(products);
               
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error completing an order", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static object GetProductIdByOrderId(long orderId)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = null;
            SqlDataAdapter adapter = null;
            try
            {
                ds = new DataSet();
                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ProductId FROM [ORDER] where OrderId=@orderId ";
                command.Parameters.AddWithValue("OrderId", orderId);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "ORDER");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["ORDER"].DefaultView;
                return dataView;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting products", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Product ProductByOrderId(long orderId)
        {
            Product product = new Product();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM PRODUCT WHERE ID IN(SELECT ProductId FROM [ORDER] where OrderId=@orderId)";
                command.Parameters.AddWithValue("OrderId", orderId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    product.ProductId = product.ProductId;
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    product.HasImages = images != 0;
                    product.Status = reader["Status"].ToString();
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return product;
        }

        public static Product ProductByIdOfOrder(long orderId)
        {
            Product product = new Product();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM PRODUCT WHERE ID IN(SELECT ProductId FROM [ORDER] where Id=@orderId)";
                command.Parameters.AddWithValue("orderId", orderId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture); 
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    product.HasImages = images != 0;
                    product.Status = reader["Status"].ToString();
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return product;
        }

        public static Order GetOrderByIdOfOrder(long orderId)
        {
            Order order = new Order();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [ORDER] where Id=@orderId";
                command.Parameters.AddWithValue("orderId", orderId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    order = new Order();
                    order.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    order.BuyerId = Int64.Parse(reader["BuyerId"].ToString(), CultureInfo.CurrentCulture);
                    order.ProductId = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    order.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    order.OrderId = Int64.Parse(reader["OrderId"].ToString(), CultureInfo.CurrentCulture);
                    order.Status = reader["Status"].ToString();
                }
                return order;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting an order", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<Order> GetOrdersByBuyerId(long buyerId)
        {
            Order order = new Order();
            Collection<Order> orders = new Collection<Order>();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM [ORDER] WHERE BuyerId=@buyerId AND Status=@status";
                command.Parameters.AddWithValue("buyerId", buyerId);
                command.Parameters.AddWithValue("status", "Completed");
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    order = new Order();
                    order.BuyerId = buyerId;
                    order.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    order.ProductId = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    order.OrderId = Int64.Parse(reader["OrderId"].ToString(), CultureInfo.CurrentCulture);
                    order.Quantity = Int16.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    order.Status = reader["Status"].ToString();
                    orders.Add(order);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting orders", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return orders;
        }
    }
}
